package com.itheima.example;

import com.itheima.pojo.Brand;
import com.itheima.util.JDBCUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BrandDao {
    /**
     *查询
     * 1. SQL:
     *          select * from tb_brand
     * 2. 参数: 需要， id
     * 3. 结果： boolean
     */
    @Test
    public void testSelectAll() {
        //获取数据库的连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //写sql
            String sql = "select * from tb_brand";

            //获取sql执行对象
            pstmt = conn.prepareStatement(sql);

            //执行sql
            rs = pstmt.executeQuery();

            //处理结果
            List<Brand> brandList = new ArrayList<>();
            Brand brand;
            while (rs.next()) {
                brand = new Brand();
                int id = rs.getInt("id");
                String brandName = rs.getString("brand_name");
                String companyName = rs.getString("company_name");
                int ordered = rs.getInt("ordered");
                String description = rs.getString("description");
                int status = rs.getInt("status");
                brand.setId(id);
                brand.setBrandName(brandName);
                brand.setCompanyName(companyName);
                brand.setOrdered(ordered);
                brand.setDescription(description);
                brand.setStatus(status);
                brandList.add(brand);
            }
            //System.out.println(brandList);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //释放资源
        JDBCUtil.close(rs,pstmt,conn);

    }

    /**
     * 添加
     * 1.SQL:
     *      insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
     * 2.参数:需要,除id以外,其他所有参数信息
     * 3.结果:boolean
     */
    @Test
    public void testAdd(){
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //获取数据库的连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //写sql
            String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";

            //获取sql执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setString(1,brandName);
            pstmt.setString(2,companyName);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);


            //执行sql
            int affNum = pstmt.executeUpdate();

            //处理结果
            System.out.println(affNum > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //释放资源
        JDBCUtil.close(rs,pstmt,conn);

    }

    /**
     * 修改
     * 1. sql:
     *      UPDATE `tb_brand` SET
     *          `brand_name` = ?,
     *          `company_name` = ?,
     *          `ordered` = ?,
     *          `description` = ?,
     *          `status` = ?
     *      WHERE `id` = ?;
     * 2. 参数:需要,包括id在内的所有参数信息
     * 3. 结果:boolean
     */
    @Test
    public void testupdate(){
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1000;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;

        //获取数据库的连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //写sql
            String sql = "UPDATE `tb_brand` SET `brand_name` = ?, `company_name` = ?, `ordered` = ?, `description` = ?, `status` = ? WHERE `id` = ?;\n";

            //获取sql执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setString(1,brandName);
            pstmt.setString(2,companyName);
            pstmt.setInt(3,ordered);
            pstmt.setString(4,description);
            pstmt.setInt(5,status);
            pstmt.setInt(6,id);


            //执行sql
            int affNum = pstmt.executeUpdate();

            //处理结果
            System.out.println(affNum > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //释放资源
        JDBCUtil.close(rs,pstmt,conn);
    }

    /**
     * 删除
     * 1.sql:
     *      delete from tb_brand where id = ?
     * 2.参数:需要,id
     * 3.结果:boolean
     */
    @Test
    public void testDelectById(){
        // 接收页面提交的参数

        int id = 4;

        //获取数据库的连接
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //写sql
            String sql = "delete from tb_brand where id = ?";

            //获取sql执行对象
            pstmt = conn.prepareStatement(sql);
            //设置参数
            pstmt.setInt(1,id);


            //执行sql
            int affNum = pstmt.executeUpdate();

            //处理结果
            System.out.println(affNum > 0);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //释放资源
        JDBCUtil.close(rs,pstmt,conn);
    }

}
